class ProcessSqlMap(object):
    CONFIG_ID_NAME = "select config_id, process_name from aoa_process_config"

    my_apply = "select t1.config as config_id,t1.process_id,t2.status_name,t1.shenuserzhi,t1.process_name,t1.type_name,t3.real_name,t1.apply_time,t1.deeply_id,t4.type_name as deeply_name from aoa_process_list as t1 inner join aoa_status_list as t2 on t1.status_id=t2.status_id  inner join aoa_user as t3 on t1.process_user_id=t3.user_id inner join aoa_type_list as t4 on t4.type_id=t1.deeply_id where t1.process_user_id={user_id}"
    # my_unreviewed = "select t1.config as config_id,t1.process_id,t1.shenuserzhi,t4.real_name,t1.process_name,t2.status_name,t1.deeply_id,t5.type_name as deeply_name,t1.type_name,t1.apply_time,t1.status_id from aoa_reviewed as t3 inner join aoa_status_list as t2 on t3.status_id=t2.status_id inner join aoa_process_list as t1 on t3.pro_id=t1.process_id inner join aoa_user as t4 on t1.process_user_id=t4.user_id inner join aoa_type_list as t5 on t5.type_id=t1.deeply_id where t3.user_id={user_id} and t2.status_name='未处理'"
    my_unreviewed = "select t4.dept_id,t1.config as config_id,t1.process_id,t1.shenuserzhi,t4.real_name,t1.process_name,t3.status_name,t1.deeply_id,t5.type_name as deeply_name,t1.type_name,t1.apply_time,t1.status_id from aoa_process_list as t1 inner join ACT_RU_IDENTITYLINK as t2 on t1.task_id=t2.TASK_ID_ inner join aoa_status_list as t3 on t1.status_id=t3.status_id inner join aoa_user as t4 on t1.process_user_id=t4.user_id inner join aoa_type_list as t5 on t5.type_id=t1.deeply_id where t2.GROUP_ID_='{position_name}'"
    my_reviewed = "select t1.config as config_id,t1.process_id,t1.shenuserzhi,t4.real_name,t1.process_name,t2.status_name,t1.deeply_id,t5.type_name as deeply_name,t1.type_name,t1.apply_time,t1.status_id from aoa_reviewed as t3 inner join aoa_status_list as t2 on t3.status_id=t2.status_id inner join aoa_process_list as t1 on t3.pro_id=t1.process_id inner join aoa_user as t4 on t1.process_user_id=t4.user_id inner join aoa_type_list as t5 on t5.type_id=t1.deeply_id where t3.user_id={user_id}"
    check_table = "select t4.user_name,t3.attachment_id as attach_id,t3.attachment_name,t1.config as config_id,t1.process_name,t1.type_name,t1.process_user_id,t1.process_des,t1.apply_time,t2.type_name as deeply_name,t1.start_time,t1.end_time from aoa_process_list as t1 inner join aoa_type_list as t2 on t1.deeply_id=t2.type_id left join aoa_attachment_list as t3 on t1.pro_file_id=t3.attachment_id left join aoa_user as t4 on t1.process_user_id=t4.user_id where process_id={process_id}"
    check_user = "select t1.real_name,t3.dept_name from aoa_user as t1 inner join aoa_dept as t3 on t1.dept_id=t3.dept_id where t1.user_id={process_user_id}"
    check_user_name = "select t1.real_name,t1.user_name,t1.dept_id,t2.dept_name,t1.position_id,t3.name as position_name from aoa_user as t1 inner join aoa_dept as t2 on t1.dept_id=t2.dept_id inner join aoa_position as t3 on t1.position_id = t3.position_id where user_id={user_id}"
    check_process = "select t1.*,t2.type_name from {table_name} as t1 inner join aoa_type_list as t2 on t1.type_id=t2.type_id where t1.pro_id={process_id}"
    check_process_not_type_id = "select * from {table_name} where pro_id={process_id}"
    check_deatilsburse = "select produce_time,subject,invoices,detailmoney,descript from aoa_detailsburse where bursment_id={bursement_id}"
    check_deatilspurch = "select name as goods_name,number as goods_bumber,subtotal,invoices,detailmoney from aoa_detail_purch where purchase_id={purchase_id}"
    check_reviewed = "select t1.advice,t1.reviewed_time,t4.status_name,t2.real_name as reviewer_name,t3.name as position_name from aoa_reviewed as t1 inner join aoa_user as t2 on t1.user_id=t2.user_id inner join aoa_position as t3 on t2.position_id=t3.position_id inner join aoa_status_list as t4 on t1.status_id=t4.status_id where pro_id={process_id}"

    check_status_id = "select status_id from aoa_status_list where status_name='{status_name}'"
    check_status_name = "select status_name from aoa_status_list where status_id='{status_id}'"
    check_user_name_position = "select t1.real_name,t1.user_name,t2.name from aoa_user as t1 inner join aoa_position as t2 on t1.position_id=t2.position_id where t1.user_id={user_id}"
    update_reviewed = "update aoa_reviewed set advice='{advice}',reviewed_time=now(),status_id={status_id} where pro_id={process_id} and user_id={user_id}"
    update_process_list = "update aoa_process_list set status_id={status_id},shenuser='{real_name}',shenuserzhi='{name}',is_checked={is_checked} where process_id={process_id}"
    update_process_list_new = "update aoa_process_list set status_id={status_id},shenuser='{real_name}',shenuserzhi='{name}' where process_id={process_id}"
    update_table = "update aoa_reviewed set advice='{advice}',reviewed_time=now(),status_id={status_id} where pro_id={process_id} and user_id={user_id}"
    create_reviewed = "insert into aoa_reviewed(advice,reviewed_time,status_id,pro_id,user_id,del) values('{advice}',now(),{next_status_id},{process_id},{user_id},0)"
    create_reviewed_examine = "insert into aoa_reviewed(advice,reviewed_time,status_id,pro_id,user_id,del) values('{advice}',now(),{next_status_id},{process_id},{next_user_id},0)"
    check_prikey = "SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='{table_name}' AND constraint_name='PRIMARY'"
    check_second_id = "SELECT {pri_name} as pri_name FROM {table_name} WHERE pro_id={process_id}"

    check_config_id = "select config_id from aoa_process_config where process_name='{type_name}'"
    create_process_list = "insert into aoa_process_list(pro_file_id,apply_time,deeply_id,end_time,process_des,process_name,is_checked,start_time,status_id,type_name,process_user_id,shenuser,config,shenuserzhi) values({attach_id},now(),{deeply_id},'{end_time}','{process_des}','{process_name}',0,'{start_time}',{status_id},'{type_name}',{process_user_id},'{shenuser}',{config_id},'{shenuserzhi}')"
    create_process_list_notime = "insert into aoa_process_list(apply_time,deeply_id,process_des,process_name,is_checked,status_id,type_name,process_user_id,shenuser,config,shenuserzhi,pro_file_id) values(now(),{deeply_id},'{process_des}','{process_name}',0,{status_id},'{type_name}',{process_user_id},'{shenuser}',{config_id},'{shenuserzhi}',{attach_id})"
    create_reviewed_new = "insert into aoa_reviewed(reviewed_time,status_id,pro_id,user_id,del) values(now(),{status_id},{process_id},{user_id},0)"
    GET_ID = "select @@IDENTITY as id"

    INSERT_PROCESS = "INSERT INTO aoa_process_list(pro_file_id,apply_time,deeply_id,end_time,process_des,process_name,is_checked,start_time,status_id,type_name,process_user_id,config,procsee_days) VALUES({attach_id},now(),{deeply_id},'{end_time}','{process_des}','{process_name}',0,'{start_time}',{status_id},'{type_name}',{process_user_id},{config_id},{leave_time})"
    INSERT_PROCESS_NOTIME = "INSERT INTO aoa_process_list(pro_file_id,apply_time,deeply_id,process_des,process_name,is_checked,status_id,type_name,process_user_id,config,procsee_days) VALUES({attach_id},now(),{deeply_id},'{process_des}','{process_name}',0,{status_id},'{type_name}',{process_user_id},{config_id},{leave_time})"
    create_holiday = "insert into aoa_holiday(leave_days, type_id, pro_id) values({leave_time},{holiday_id},{process_id})"
    create_go_out = "insert into aoa_go_out(address, pro_id,leave_time) values('{address}',{process_id},{leave_time})"
    create_resign = "insert into aoa_resign(is_finish, nofinish,hand_user,pro_id) values({is_finish},'{nofinish}',{hand_user},{process_id})"
    create_bursement = "insert into aoa_bursement(name,all_money,allinvoices,pro_id) values('{name}',{all_money},{allinvoices},{process_id})"
    create_detailsburse = "insert into aoa_detailsburse(produce_time,subject,descript,invoices,detailmoney,bursment_id) values('{produce_time}','{subject}','{descript}',{invoices},{detailmoney},{bursment_id})"
    create_purchase = "insert into aoa_purchase(name,all_money,pro_id) values('{name}',{all_money},{process_id})"
    create_detailspurchase = "insert into aoa_detail_purch(name,number,invoices,detailmoney,subtotal,purchase_id) values('{goods_name}','{goods_number}',{invoices},{detailmoney},{subtotal},{purchase_id})"
    create_free_stop = "insert into aoa_free_stop(consume_money, derate_level, derate_money, license, pro_id) values({consume_money},{derate_level},{derate_money},'{license}',{process_id})"
    create_special_car = "insert into aoa_special_car(derate_level,license, pro_id) values({derate_level},'{license}',{process_id})"
    create_overtime = "INSERT INTO aoa_overtime(adress,time,pro_id) VALUES('{adress}',{leave_time},{process_id})"
    create_evection = "INSERT INTO aoa_evection(adress,leave_days,pro_id,traffic) VALUES('{adress}',{leave_time},{process_id},{type_id})"
    create_regular = "INSERT INTO aoa_regular(dobetter,experience,pro_id,advice) VALUES('{dobetter}','{experience}',{process_id},'{advice}')"
    create_borrow = "INSERT INTO aoa_borrow(employ_time,money_big,pro_id,pay_type,money,remark) VALUES('{employ_time}','{money_big}',{process_id},{pay_type},{money},'{remark}')"
    create_borrow_noremark = "INSERT INTO aoa_borrow(employ_time,money_big,pro_id,pay_type,money) VALUES('{employ_time}','{money_big}',{process_id},{pay_type},{money})"
    create_payment = "INSERT INTO aoa_payment(remark,end_time,money_big,pro_id,pay_type,money,bank_address,bank_num) VALUES('{remark}','{finally_time}','{money_big}',{process_id},{pay_type},{money},'{bank_address}',{bank_num})"
    create_payment_noremark = "INSERT INTO aoa_payment(end_time,money_big,pro_id,pay_type,money,bank_address,bank_num) VALUES('{finally_time}','{money_big}',{process_id},{pay_type},{money},'{bank_address}',{bank_num})"
    create_chapter = "INSERT INTO aoa_chapter(chapter_type,pro_id) VALUES({chapter_type},{process_id})"
    create_contract = "INSERT INTO aoa_contract(contact, contract_num, contract_name, customer, money, money_big, outline, remark, pro_id) VALUES('{contact}','{contract_num}','{contract_name}','{customer}',{money},'{money_big}','{outline}','{remark}',{process_id})"
    create_contract_noremark = "INSERT INTO aoa_contract(contact, contract_num, contract_name, customer, money, money_big, outline, pro_id) VALUES('{contact}','{contract_num}','{contract_name}','{customer}',{money},'{money_big}','{outline}',{process_id})"
    create_special_matter = "INSERT INTO aoa_process_list(apply_time, deeply_id, process_des, process_name, is_checked, start_time, status_id, type_name, pro_file_id, process_user_id, config) " \
                            "VALUE(now(),{deeply_id},'{process_des}','{process_name}',0,'{start_time}',{status_id},'{type_name}',{attach_id},{process_user_id},{config_id})"


    # check_type_id = "select type_id from aoa_type_list where type_name='{holiday_name}'"

    process_list = "select t1.config as config_id,t1.process_id,t2.status_name,t1.shenuserzhi,t1.process_name,t1.type_name,t3.real_name,t1.apply_time,t1.deeply_id,t4.type_name as deeply_name from aoa_process_list as t1 inner join aoa_status_list as t2 on t1.status_id=t2.status_id  inner join aoa_user as t3 on t1.process_user_id=t3.user_id inner join aoa_type_list as t4 on t1.deeply_id=t4.type_id"

    check_config_name = "select process_name as type_name from aoa_process_config where config_id={config_id}"

    check_derate_level = "select type_name from aoa_type_list where type_id={derate_level}"
    check_type = "SELECT type_id,type_name FROM aoa_type_list WHERE type_id={type_id}"

    # check_holiday_name = "select type_name from aoa_type_list where type_id={holiday_id}"

    check_subject_name = "select name from aoa_subject where subject_id={subject_id}"



    FILTER_PARAMS={
        "query_str": "t1.process_name like '%{query_str}%'",
    }

